import logging
import math
from idlelib.query import Query
from typing import Optional, List

from fastapi import APIRouter, Depends, Query, Path
from pydantic import Field, BaseModel
from sqlalchemy import text
from sqlalchemy.exc import SQLAlchemyError
from sqlalchemy.orm import Session
from commons.BaseResponse import BaseResponse
from commons.PaginationBase import PaginationBase
from datebase import get_db
from utils.EncryptionUtil import oauth2_scheme, EncryptionMiddleware

tc_daily_account_router = APIRouter(prefix="/tcDailyaccount", dependencies=[Depends(oauth2_scheme)])


class ChargePrepaymentSearch(PaginationBase):
    # 筛选条件
    fsiteNo: Optional[str] = Field("", description="站点编号")
    minTime: Optional[str] = Field("", description="开始日期(yyyyMMdd)")
    maxTime: Optional[str] = Field("", description="结束日期(yyyyMMdd)")
    fbillno: Optional[str] = Field("", description="单据编号(支持前缀匹配)")
    ftaskno: Optional[str] = Field("", description="任务编号")


@tc_daily_account_router.get("/list", summary="收费预存查询接口")
def list_daily_account(search: ChargePrepaymentSearch = Query(), db: Session = Depends(get_db)):
    param = {}
    sql = "select count(1) from m_tc_dailyaccount where 1=1"
    if search.fsiteNo != "":
        sql = sql + " and fsite_no = :fsiteNo "
        param["fsiteNo"] = search.fsiteNo
    if search.fbillno != "":
        sql = sql + " and fbillno like concat('%', :fbillno ,'%')"
        param["fbillno"] = search.fbillno
    if search.ftaskno != "":
        sql = sql + " and ftaskno = :ftaskno "
        param["ftaskno"] = search.ftaskno
    if (search.minTime != "") and (search.maxTime != ""):
        sql = sql + " and to_char(fchargedate,'yyyymmdd') between :minTime  and :maxTime "
        param["minTime"] = search.minTime
        param["maxTime"] = search.maxTime
    count = db.execute(text(sql), param).scalar()

    param = {}
    sql = "select fbillno,fsite_no,ftaskno,fuseuser,fchargedate,fchargetype,fchargeamt,fstate,fexplanation,fupddate,foperator"
    sql = sql + " from m_tc_dailyaccount where 1=1"
    if search.fsiteNo != "":
        sql = sql + " and fsite_no = :fsiteNo "
        param["fsiteNo"] = search.fsiteNo
    if search.fbillno != "":
        sql = sql + " and fbillno like concat('%', :fbillno ,'%')"
        param["fbillno"] = search.fbillno
    if search.ftaskno != "":
        sql = sql + " and ftaskno = :ftaskno "
        param["ftaskno"] = search.ftaskno
    if (search.minTime != "") and (search.maxTime != ""):
        sql = sql + " and to_char(fchargedate,'yyyymmdd') between :minTime  and :maxTime "
        param["minTime"] = search.minTime
        param["maxTime"] = search.maxTime
    if search.order == "desc":
        sql = sql + " order by  fbillno desc "
    else:
        sql = sql + " order by  fbillno  "
    sql = sql + " limit :limit offset :offset "
    param["limit"] = search.limit
    param["offset"] = search.offset
    data = db.execute(text(sql), param).fetchall()

    data_list = []
    for key in range(len(data)):
        d1 = {}
        d1['fbillno'] = data[key][0]
        d1['fsiteno'] = data[key][1]
        d1['ftaskno'] = data[key][2]
        d1['fuseuser'] = data[key][3]
        d1['fchargedate'] = data[key][4]
        d1['fchargetype'] = data[key][5]
        d1['fchargeamt'] = data[key][6]
        d1['fstate'] = data[key][7]
        d1['fexplanation'] = data[key][8]
        d1['fupddate'] = data[key][9]
        d1['foperator'] = data[key][10]
        data_list.append(d1)
    info_dict = {"list": data_list,
                 "limit": search.limit,
                 "page": search.pageIndex,
                 "pages": (count + search.limit - 1) // search.limit,
                 "total": count}
    return BaseResponse.success(data=info_dict)


class ChargePrepaymentCreate(BaseModel):
    """收费预存新增请求参数"""
    fbillno: str = Field(..., description="单据编号")
    fchargeamt: float = Field(..., description="预存金额")
    fchargedate: str = Field(..., description="预存日期(格式需与数据库存储一致，如'2025-06-26')")
    fchargetype: int = Field(..., description="预存类型")
    foperator: str = Field(..., description="操作人ID")
    fsiteNo: str = Field(..., description="站点编号")
    ftaskno: str = Field(..., description="任务编号")
    fupddate: str = Field(..., description="更新日期(格式需与数据库存储一致，如'2025-06-26 12:00:00')")
    fuseuser: str = Field(..., description="使用人ID")
    fteamno: str = Field(..., description="团队编号")
    fexplanation: Optional[str] = Field("", description="备注说明")


@tc_daily_account_router.post("/create", summary="收费预存新增接口")
def create_daily_account(create: ChargePrepaymentCreate, db: Session = Depends(get_db)):
    try:
        with db.begin():
            sql = "insert into m_tc_dailyaccount(fbillno,fsite_no,ftaskno,fuseuser,fchargedate,fchargetype,fchargeamt,fstate,fexplanation,fupddate,foperator)"
            sql = sql + " values( :fbillno , :fsiteNo , :ftaskno , :fuseuser , :fchargedate , :fchargetype , :fchargeamt ,0, :fexplanation , :fupddate , :foperator )"
            db.execute(text(sql), {
                "fbillno": create.fbillno,
                "fsiteNo": create.fsiteNo,
                "ftaskno": create.ftaskno,
                "fuseuser": create.fuseuser,
                "fchargedate": create.fchargedate,
                "fchargetype": create.fchargetype,
                "fchargeamt": create.fchargeamt,
                "fexplanation": create.fexplanation,
                "fupddate": create.fupddate,
                "foperator": create.foperator
            })
            # 返回内容
            d1 = {}
            d1['fbillno'] = create.fbillno
            d1['fchargeamt'] = create.fchargeamt
            d1['fchargedate'] = create.fchargedate
            d1['fchargetype'] = create.fchargetype
            d1['fexplanation'] = create.fexplanation
            d1['foperator'] = create.foperator
            d1['fsiteNo'] = create.fsiteNo
            d1['fstate'] = 0
            d1['ftaskno'] = create.ftaskno
            d1['fupddate'] = create.fupddate
            d1['fuseuser'] = create.fuseuser
            d1['fteamno'] = create.fteamno
            d1['ftaskname'] = None
            d1['fteamname'] = None
            return BaseResponse.success(data=d1)
    except SQLAlchemyError as e:
        # 发生异常时自动回滚
        db.rollback()
        # 记录错误日志
        logging.error(f"数据库错误: {str(e)}")
        # 返回错误响应
        return BaseResponse.error(message="数据库操作失败")
    except Exception as e:
        db.rollback()
        logging.error(f"未知错误: {str(e)}")
        return BaseResponse.error(message="令牌过期")


@tc_daily_account_router.post("/update", summary="")
def update_daily_account(update: ChargePrepaymentCreate, db: Session = Depends(get_db),
                         token: str = Depends(oauth2_scheme)):
    try:
        user_id = EncryptionMiddleware.get_current_user(token)
        with db.begin():
            sql = "update m_tc_dailyaccount "
            sql = sql + " set fchargedate= :fchargedate ,ftaskno= :ftaskno ,fexplanation= :fexplanation "
            sql = sql + ",fchargeamt= :fchargeamt ,fuseuser= :fuseuser "
            sql = sql + ",fupddate=now(),foperator= :userid "
            sql = sql + " where fbillno= :fbillno "
            db.execute(text(sql), {
                "fchargedate": update.fchargedate,
                "ftaskno": update.ftaskno,
                "fexplanation": update.fexplanation,
                "fchargeamt": update.fchargeamt,
                "fuseuser": update.fuseuser,
                "userid": user_id,
                "fbillno": update.fbillno
            })
            return BaseResponse.success()
    except SQLAlchemyError as e:
        # 发生异常时自动回滚
        db.rollback()
        # 记录错误日志
        logging.error(f"数据库错误: {str(e)}")
        # 返回错误响应
        return BaseResponse.error(message="数据库操作失败")
    except Exception as e:
        db.rollback()
        logging.error(f"未知错误: {str(e)}")
        return BaseResponse.error(message="令牌过期")


class Search(BaseModel):
    fbillno: str = Field(..., description="单号")


@tc_daily_account_router.get("/cance", summary="")
def cancel_daily_account(search: Search = Query(), db: Session = Depends(get_db)):
    try:
        with db.begin():
            sql = "update m_tc_dailyaccount set fstate=2 where fbillno= :fbillno "
            db.execute(text(sql), {
                "fbillno": search.fbillno
            })
            return BaseResponse.success()
    except SQLAlchemyError as e:
        # 发生异常时自动回滚
        db.rollback()
        # 记录错误日志
        logging.error(f"数据库错误: {str(e)}")
        # 返回错误响应
        return BaseResponse.error(message="数据库操作失败")
    except Exception as e:
        db.rollback()
        logging.error(f"未知错误: {str(e)}")
        return BaseResponse.error(message="令牌过期")
